<?php
/**
 *
 * @category    API
 * @package     Api_Song
 * @subpackage  Model
 * @author      trungpm
 */
class Api_Model_Song {
    const table_name = 'tblvgt_song';
    const table_object = 'tblvgt_object';

    /* Check that the name exists and is 300 characters or less long */
    function VerifySongName($SongName, $i, $Errors) {
        if ((strlen ( $SongName ) < 1) or (strlen ( $SongName ) > 300)) {
            $Errors [$i] = IncorrectSongNameLength;
        }
        return $Errors;
    }

    /*
     * author: trungpm
     */
    function VerifySongNameExists($SongName, $i, $Errors, $db) {
        $Searchsql = 'SELECT count(song_id) as c FROM tblvgt_song WHERE locked = 0 AND song_name=N\'' . $SongName . '\'';
        $SearchResult = DB_query ( $Searchsql, $db );
        $answer = DB_fetch_array ( $SearchResult );
        if ($answer ['c'] != 0) {
            $Errors [$i] = SongNameAlreadyExists;
        }
        DB_free_result ( $SearchResult );
        return $Errors;
    }
    function checkFieldExisted($table, $field, $value) {
    }

    /*
     * author: trungpm
     */
    function VerifySongNumberExists($SongNumber, $SongPartnerId, $i, $Errors, $db) {
        $Searchsql = 'SELECT count(song_id) as c FROM tblvgt_song
        WHERE locked = 0 AND song_number=N\'' . $SongNumber . '\' AND partner_id = ' . $SongPartnerId;
        $SearchResult = DB_query ( $Searchsql, $db );
        $answer = DB_fetch_array ( $SearchResult );
        if ($answer ['c'] != 0) {
            $Errors [$i] = SongNumberAlreadyExists;
        }
        DB_free_result ( $SearchResult );
        return $Errors;
    }

    /*
     * author: trungpm
     */
    function GetSongByNumber($SongNumber, $SongPartnerId, $user, $password) {
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        $SongNumber = DB_escape_string ( $SongNumber );
        $Errors = $this->VerifySongNumberExists ( $SongNumber, $SongPartnerId, sizeof ( $Errors ), $Errors, $db );
        if (sizeof ( $Errors ) == 0) {
            return $Errors;
        }
        $sql = 'SELECT song.song_id,
                    song.song_number,
                    song.song_name,
                    song.song_unsigned_name,
                    song.song_price,
                    song.partner_id,
                    song.singer_id,
                    song.song_singer_sing,
                    singer.object_name as singer_name,
                    composer.object_name as composer_name,
                    song.composer_id,
                    song.song_name,
                    song.song_singer_authority,
                    song.song_composer_authority,
                    song.song_release_authority
                FROM tblvgt_song as song
                LEFT JOIN tblvgt_object as singer
                ON song.singer_id = singer.object_id
                LEFT JOIN tblvgt_object as composer
                ON song.composer_id = composer.object_id
                WHERE song.locked = 0 AND song.partner_id = ' . $SongPartnerId . ' AND song_number=N\'' . $SongNumber . '\'';
        $result = DB_Query ( $sql, $db );
        if (sizeof ( $Errors ) != 0) {
            $arrResult = DB_fetch_array ( $result );
            DB_free_result ( $result );
            return $arrResult;
        } else {
            return $Errors;
        }
    }

    /*
     * author: trungpm
     */
    function GetSongByName($SongName, $user, $password) {
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        $SongName = DB_escape_string ( $SongName );
        $sql = 'SELECT song_id,
                    song_number,
                    song_name,
                    song_unsigned_name,
                    song_price
                FROM tblvgt_song WHERE locked = 0 AND song_name=N\'' . $SongName . '\'';
        $result = DB_Query ( $sql, $db );

        $arrResult = DB_fetch_array ( $result );
        DB_free_result ( $result );
        return $arrResult;
    }

    /* Insert a new Song */
    function InsertSong($SongDetails, $user, $password) {
        $Errors = array ();
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        foreach ( $SongDetails as $key => $value ) {
            $SongDetails [$key] = DB_escape_string ( $value );
        }
        $Errors = $this->VerifySongNumberExists ( $SongDetails ['song_number'], $SongDetails ['partner_id'], sizeof ( $Errors ), $Errors );
        $FieldNames = '';
        $FieldValues = '';
        foreach ( $SongDetails as $key => $value ) {
            $FieldNames .= $key . ', ';
            $FieldValues .= 'N\'' . str_replace ( "\'", "''", $value ) . '\', ';
        }
        $sql = 'INSERT INTO tblvgt_song (' . substr ( $FieldNames, 0, - 2 ) . ') ' . 'VALUES (' . substr ( $FieldValues, 0, - 2 ) . ') ';
        if (sizeof ( $Errors ) == 0) {
            $result = DB_Query ( $sql, $db );
            if (! $result) {
                $Errors [0] = DatabaseInsertFailed;
            }
        }
        return $Errors;
    }
    function ModifySong($SongId, $SongDetails, $user, $password) {
        $Errors = array ();
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        foreach ( $SongDetails as $key => $value ) {
            $SongDetails [$key] = DB_escape_string ( $value );
        }
        $sql = 'UPDATE tblvgt_song SET ';
        foreach ( $SongDetails as $key => $value ) {
            $sql .= $key . '=N\'' . str_replace ( "\'", "''", $value ) . '\', ';
        }
        $sql = substr ( $sql, 0, - 2 ) . ' WHERE song_id=' . $SongId;

        if (sizeof ( $Errors ) == 0) {
            $result = DB_Query ( $sql, $db );
            if (! isset ( $result ) || ! $result) {
                $Errors [0] = DatabaseUpdateFailed;
            } else {
                $Errors = array ();
            }
        }
        return $Errors;
    }

    function loadSongs($limit = null, $start = null, &$total, $user, $password, $condition = array()) {
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }

        $where = '';
        if (! empty ( $condition )) {
            $arr = array ();
            foreach ( $condition as $cond ) {
                $arr [] = $cond ['field'] . ' ' . $cond ['relation'] . ' ' . $cond ['value'];
            }
            $where = implode ( " AND ", $arr );
        }

        if (! empty ( $where )) {
            $where = "WHERE locked = 0 AND $where";
        }
        $sql = "SELECT COUNT(song_id) AS size FROM tblvgt_song $where";
        $result = DB_Query ( $sql, $db );
        $obj = DB_fetch_object ( $result );
        $total = intval ( $obj->size );
        DB_free_result ( $result );

        $sql = "SELECT @rownum:=@rownum+1 AS rownum,
                    s.song_id,
                    s.type_id ,
                    s.partner_id,
                    s.singer_id,
                    oSinger.object_number as singer_number,
                    oComposer.object_number as composer_number,
                    s.composer_id,
                    s.song_number,
                    s.song_name,
                    s.song_unsigned_name,
                    s.song_price,
                    s.song_singer_authority,
                    s.song_composer_authority,
                    s.song_release_authority,
                    oSinger.object_name AS singer_name,
                    s.song_singer_sing,
                    oComposer.object_name As composer_name
                FROM tblvgt_song AS s
                LEFT JOIN tblvgt_object AS oSinger ON s.singer_id = oSinger.object_id
                LEFT JOIN tblvgt_object AS oComposer ON s.composer_id = oComposer.object_id
                JOIN (SELECT @rownum:=0) r ON 1 = 1
                $where";
        if (isset ( $start ) && isset ( $limit )) {
            $sql .= " LIMIT $start, $limit";
        }
        $result = DB_Query ( $sql, $db );

        if (DB_has_rows ( $result ) == 0) {
            return null;
        } else {
            return $result;
        }
    }

    /*
     * Insert a new object and return key huudatxm
     */
    function insert($data, $user, $password) {
        $Errors = array ();
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }

        $FieldNames = '';
        $FieldValues = '';
        foreach ( $data as $key => $value ) {
            $FieldNames .= $key . ", ";
            $FieldValues .= 'N\'' . $value . '\', ';
        }
        // $FieldNames .= 'created_date, ';
        // $FieldValues .= 'N\''.date('Y-m-d').'\', ';
        $FieldNames .= 'created_by, ';
        $FieldValues .= 'N\'' . $_SESSION ['UserID'] . '\', ';
        $FieldNames .= 'updated_date, ';
        $FieldValues .= 'N\'' . date ( 'Y-m-d' ) . '\', ';
        $FieldNames .= 'updated_by, ';
        $FieldValues .= 'N\'' . $_SESSION ['UserID'] . '\', ';

        $sql = 'INSERT INTO tblvgt_song (' . substr ( $FieldNames, 0, - 2 ) . ') ' . ' VALUES (' . substr ( $FieldValues, 0, - 2 ) . ') ';

        if (sizeof ( $Errors ) == 0) {
            DB_Query ( $sql, $db );
            return $_SESSION ['LastInsertId'];
        }
        return 0;
    }

    /*
     * update huudatxm
     */
    function update($data, $user, $password) {
        $Errors = array ();
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }

        $val_str = $data ['field'] . ' = \'' . $data ['value'] . '\'';
        $val_str .= ', updated_date = \'' . date ( 'Y-m-d' ) . '\' ';
        $val_str .= ', updated_by = \'' . $_SESSION ['UserID'] . '\' ';

        $sql = 'UPDATE tblvgt_song SET ' . $val_str;
        $sql .= ' WHERE song_id = ' . $data ['song_id'];

        if (sizeof ( $Errors ) == 0) {
            return DB_Query ( $sql, $db );
        }
        return 0;
    }

    /*
     * delete huudatxm
     */
    function delete($song_id, $user, $password) {
        $Errors = array ();
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }

        $checkId = $this->verifyFieldExist ( 'song_id', $song_id );
        if ($checkId == 1) {
            $val_str = 'locked = 1';
            $val_str .= ', updated_date = N\'' . date ( 'Y-m-d' ) . '\' ';
            $val_str .= ', updated_by = N\'' . $_SESSION ['UserID'] . '\' ';
            $sql = "UPDATE " . self::table_name . " SET $val_str WHERE song_id = $song_id";

            if (sizeof ( $Errors ) == 0) {
                return DB_Query ( $sql, $db );
            }
        }
        return null;
    }

    /**
     * load datas with paginates
     */
    function getSongPaginates($limit, $start, &$total, $conds) {
        // Check user permission
        $db = db ( $_SESSION ['UserID'], $_SESSION ['Password'] );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        ;
        // Get search conditions
        $condsStr = null;
        if (! empty ( $conds )) {
            $arr = array ();
            foreach ( $conds as $cond ) {
                $arr [] = 's.' . $cond ['field'] . ' ' . $cond ['relation'] . ' ' . $cond ['value'];
            }
            $condsStr .= implode ( " AND ", $arr );
        }
        ;

        $where = '';
        if (! empty ( $condsStr )) {
            $where = 'WHERE ' . $condsStr;
        }
        ;
        // Get query string for size
        $sql = "SELECT COUNT(*) AS size
                FROM " . self::table_name . " AS s $where";
        // Get size
        $result = DB_Query ( $sql, $db );
        $obj = DB_fetch_object ( $result );
        $total = intval ( $obj->size );
        DB_free_result ( $result );

        // Get query string with row_num
        $query = "SELECT @rownum:=@rownum+1 AS rownum,
                    s.song_id,
                    s.type_id ,
                    s.partner_id,
                    s.singer_id,
                    oSinger.object_number as singer_number,
                    oComposer.object_number as composer_number,
                    s.composer_id,
                    s.song_number,
                    s.song_name,
                    s.song_unsigned_name,
                    s.song_price,
                    s.song_singer_authority,
                    s.song_composer_authority,
                    s.song_release_authority,
                    DATE_FORMAT(s.created_date,'%d/%m/%Y') AS created_date,
                    DATE_FORMAT(s.ended_date,'%d/%m/%Y') AS ended_date,
                    oSinger.object_name AS singer_name,
                    s.song_singer_sing,
                    oComposer.object_name As composer_name
                FROM " . self::table_name . " AS s
                LEFT JOIN " . self::table_object . " AS oSinger ON s.singer_id = oSinger.object_id
                LEFT JOIN " . self::table_object . " AS oComposer ON s.composer_id = oComposer.object_id
                JOIN (SELECT @rownum:=0) r ON 1 = 1
                $where";

        // Get query string with paginate
        if (isset ( $start ) && isset ( $limit )) {
            $query .= " LIMIT $start, $limit";
        }

        // Execute query
        $result = DB_Query ( $query, $db );
        // Return result
        if (DB_has_rows ( $result ) == 0) {
            return null;
        } else {
            return $result;
        }
    }

    /*
     * Verify field existed.
     */
    function verifyFieldExist($field, $value) {
        // Check user permission
        $db = db ( $_SESSION ['UserID'], $_SESSION ['Password'] );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        ;
        $Searchsql = "SELECT count(*) as c FROM " . self::table_name . " WHERE $field = N'$value'";
        $SearchResult = DB_query ( $Searchsql, $db );
        $answer = DB_fetch_array ( $SearchResult );
        if ($answer ['c'] != 0) {
            return 1;
        }
        ;
        return 0;
    }

    /*
     * Kiem tra bai hat doc quyen co con trong tgian doc quyen neu het thi chuyen ve trang thai khong doc quyen huudatxm
     */
    function unExecuteSong($ended_date, $user, $password) {
        $Errors = array ();
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }

        $val_str .= ', updated_date = N\'' . date ( 'Y-m-d' ) . '\' ';
        $val_str .= ', updated_by = N\'' . $user . '\' ';
        $sql = "UPDATE " . self::table_name . " SET song_release_authority = 0
        WHERE locked = 0 AND ended_date <  N'$ended_date'";

        if (sizeof ( $Errors ) == 0) {
            return DB_Query ( $sql, $db );
        }

        return null;
    }
}